package xine.demo.ssm.util;

import java.io.*;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

/**
 * Excel工具类
 */
public class ExcelUtil {

	private static final String fileName = "test1";

	/**
	 * 获取单元格的值的字符串格式
	 * @param cell
	 * @return
	 */
	private static String getCellValueAsString(DataFormat dataFormat, Cell cell){
		if(cell == null) return null;
		String result = "";

		switch(cell.getCellTypeEnum()){
			case NUMERIC:// 数字类型
				if(DateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式

				}
				else {
					double value = cell.getNumericCellValue();
					CellStyle style = cell.getCellStyle();
					String temp = (style == null) ? "" : style.getDataFormatString();
					// 单元格设置成常规
					if(temp != null && temp.equals("General")) {
						//result = new ExcelNumberFormat("#.######").getFormat();
					}
					else{
						//result = StringUtils.getNumFormat(value, "0.00");
					}
				}
				break;
			case FORMULA:// 公式类型
				CellStyle style = cell.getCellStyle();
				String temp = (style == null) ? "" : style.getDataFormatString();
				if(temp != null && (temp.indexOf("0")==0 || temp.indexOf("#") == 0)){
					//result = StringUtils.getNumFormat(cell.getNumericCellValue(), "0.00");
				}
				else{
					System.out.println("temp=" + temp);
					try{
						RichTextString rts = cell.getRichStringCellValue();
						result = (rts == null) ? "" : rts.toString();
					}
					catch(Exception ex){
						System.out.println(ex.getMessage());
						result = cell.getCellFormula();
					}
				}

				break;
			case STRING:// String类型
				RichTextString rts = cell.getRichStringCellValue();
				result = (rts == null) ? "" : rts.toString();
				break;
			case BLANK:
			default:
				result = "";
		}

		return result;
	}

	/** 创建xls格式的文件
	 * @throws IOException
	 */
	public static void writeHSSF() throws IOException {
		Workbook wb = new HSSFWorkbook();//创建xls文件
		//Workbook wb = new XSSFWorkbook();//创建xlsx文件

		//创建安全的sheet名
		//String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); //returns " O'Brien's sales   "
		String safeName = "sheet1";
		Sheet sheet = wb.createSheet(safeName);

		CreationHelper createHelper = wb.getCreationHelper(); //用于创建富文本字符串，达到一个单元格内的文字不同的目的

		//Create a row and put some cells in it. Rows are 0 based.
		Row row = sheet.createRow(0);

		/* ---------        创建单元格        -------------- */
		//Create a cell and put a value in it.
		Cell cell = row.createCell(0);
		cell.setCellValue("1");

		cell = row.createCell(1);
		cell.setCellValue("abc");

		//在一行内创建单元格、并设置其值
//	    row.createCell(1).setCellValue(1.2);
//	    row.createCell(2).setCellValue(
//	         createHelper.createRichTextString("This is a string"));
//	    row.createCell(3).setCellValue(true);
//
//
//	    //创建日期格式的字符串，若不格式化则为一个数值
//	    // we style the second cell as a date (and time).  It is important to
//	    // create a new cell style from the workbook otherwise you can end up
//	    // modifying the built in style and effecting not only this cell but other cells.
//	    CellStyle cellStyle = wb.createCellStyle();
//	    cellStyle.setDataFormat(
//	        createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
//	    cell = row.createCell(1);
//	    cell.setCellValue(new Date());
//	    cell.setCellStyle(cellStyle);
//
//	    //you can also set date as java.util.Calendar
//	    cell = row.createCell(2);
//	    cell.setCellValue(Calendar.getInstance());
//	    cell.setCellStyle(cellStyle);
//
//	    row = sheet.createRow(1);
//	    row.createCell(0).setCellValue(1.1);//1.1 右对齐
//	    row.createCell(1).setCellValue(new Date());//43278.6461900232 数值类型  右对齐
//	    row.createCell(2).setCellValue(Calendar.getInstance());//43278.6461900232 数值类型  右对齐
//	    row.createCell(3).setCellValue("a string");//a string 左对齐
//	    row.createCell(4).setCellValue(true);//TRUE，居中
//	    row.createCell(5).setCellType(CellType.ERROR);//#VALUE! 居中


		OutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(fileName +".xls");
			//fileOut = new FileOutputStream(fileName + ".xlsx");

			wb.write(fileOut);
		}catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		catch(IOException e){
			e.printStackTrace();
		}finally {
			wb.close();
		}

		//fileOut.write(3);
	}


}